package guoan.repository;

import guoan.domain.CoachItem;

import java.util.List;

import org.joda.time.DateTime;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface CoachItemRepository extends PagingAndSortingRepository<CoachItem, Long>,
		JpaSpecificationExecutor<CoachItem> {

	Long countByCoachId(Long id);

	List<CoachItem> findByCoachIdOrderByOpenHoursAsc(Long coachId);

	List<CoachItem> findByCoachIdAndOpenHours(Long coachId, String openHours);

	@Query(value = "SELECT open_hours FROM coach_item WHERE coach_id = :coachId  AND status = '正常 '  ORDER BY open_hours ASC", nativeQuery = true)
	List<String> findOpenHoutsByCoachId(@Param("coachId") Long coachId);

	List<CoachItem> findByCoachId(Long coachId);

	Long countByStudentIdAndCoachId(Long studentId, Long coachId);

	Long countByStudentIdAndCoachIdAndOpenHours(Long studentId, Long coachId, String itemHours);

	Long countByCoachIdAndOpenHours(Long coachId, String itemHours);

	List<CoachItem> findByStudentId(Long studentId);

	CoachItem findByIdAndStudentId(Long coachItemId, Long studentId);

	List<CoachItem> findByStudentIdAndExamTimeGreaterThanEqualOrderByExamTimeAsc(Long studentId, DateTime toDate);

	/**
	 * 学员的正常的可查看的预约
	 * 
	 * @param studentId
	 * @param statusDefalut
	 * @param toDate
	 * @return
	 */
	List<CoachItem> findByStudentIdAndStatusAndExamTimeGreaterThanEqualOrderByExamTimeAsc(Long studentId,
			String statusDefalut, DateTime toDate);

	/**
	 * 正常状态的报名练车数量
	 * 
	 * @param id
	 * @param statusDefalut
	 * @return
	 */
	Long countByCoachIdAndStatus(Long id, String statusDefalut);

	/**
	 * 正常状态下的用于是否有预约
	 * 
	 * @param studentId
	 * @param coachId
	 * @param statusDefalut
	 * @return
	 */
	Long countByStudentIdAndCoachIdAndStatus(Long studentId, Long coachId, String statusDefalut);

	/**
	 * 正常状态下的时间段用于是否有预约
	 * 
	 * @param studentId
	 * @param coachId
	 * @param itemHours
	 * @param statusDefalut
	 * @return
	 */
	Long countByStudentIdAndCoachIdAndOpenHoursAndStatus(Long studentId, Long coachId, String itemHours,
			String statusDefalut);

	Long countByCoachIdAndOpenHoursAndStatus(Long coachId, String itemHours, String statusDefalut);

	List<CoachItem> findByCoachIdAndStatusOrderByOpenHoursAsc(Long coachId, String statusDefalut);

	@Modifying
	@Query(value = "DELETE FROM coach_item WHERE date(exam_time) < date(now())", nativeQuery = true)
	void removeLast();

}
